﻿using L.EntityFramework;
using MySql.Data.MySqlClient;
using NB.MGJ.Common.Utility;
using NB.MGJ.FrameWork;
using NB.MGJ.Model;
using NB.MGJ.Model.AntiFleeingEntity;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web.Mvc;

namespace NB.MGJ.Service.CodeManage
{
    public class CodeRelateService
    {
        public BaseRepository _baseRepository = new BaseRepository();
        public CodeCommonService _CodeTableService = new CodeCommonService();

        public IEnumerable<SelectListItem> GetGenCodeBatchList(int enteprriseId, int allotType)
        {
            var _ManagerService = new ManagerService();

            var Emodel = _ManagerService.GetEnterpriseModel_Eid(enteprriseId);

            if (Emodel.HeadId != null && Emodel.HeadId != 1)
            {
                enteprriseId = Convert.ToInt32(Emodel.HeadId);
            }

            if(Emodel.HeadId.HasValue&&(Emodel.HeadId == 1 || Emodel.HeadId == 0))
            //if (Emodel.HeadId == 1 || Emodel.HeadId == 0)
            {
                var _sql = string.Format("SELECT ID,Batch FROM codemanage_gencodebatch WHERE EnterpriseId = {0} ", enteprriseId);
                return _baseRepository.ExecuteSqlCollection<CodeManage_GenCodeBatch>(_sql).Select(p =>
                {
                    return new SelectListItem
                    {
                        Text = p.Batch,
                        //Value = p.ButtonId.ToString()
                        Value = p.Batch
                    };
                });
            }
            else
            {
                var _sql = string.Format("SELECT a.ID,A.Batch FROM	codemanage_gencodebatch a LEFT JOIN codemanage_allot b ON a.ID =b.AllotBatchId where a.EnterpriseId={0} AND b.hasverifie = 1 AND b.T_EnterpriseId = {1} AND a.ProductId = 0 AND a.ViewFlag = 1 GROUP BY a.ID,A.Batch", enteprriseId, Emodel.ID);

                if (allotType == 1)
                {
                    _sql += " AND a.ProductId = 0 AND a.ViewFlag =1 ";
                }
                return _baseRepository.ExecuteSqlCollection<CodeManage_GenCodeBatch>(_sql).Select(p =>
                {
                    return new SelectListItem
                    {
                        Text = p.Batch,
                        //Value = p.ButtonId.ToString()
                        Value = p.Batch
                    };
                });
            }
        }

        //新增关联信息
        //public int AddRelation(L.EntityModel.CodeManage_Allot model)
        //{
        //    baseRepository2.Insert<L.EntityModel.CodeManage_Allot>(model);
        //    return model.ID;
        //}

        public int AddRelation(CodeManage_Allot model)
        {
            return _baseRepository.InsertReturnID<CodeManage_Allot>(model);
        }

        //批量插入明细
        public void BatchAddAllotDetail(string[] codes, int allotId)
        {
            string sql = "INSERT INTO CODEMANAGE_ALLOTDETAILS (WLCODE,CREATEDATE,ALLOTID) VALUES";

            using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["mgj_basemysql"].ConnectionString))
            {
                using (MySqlCommand cmd = conn.CreateCommand())
                {
                    conn.Open();
                    cmd.CommandType = CommandType.Text;
                    foreach (var item in codes)
                    {
                        sql += "('" + item + "','" + DateTime.Now + "'," + allotId + "),";
                    }
                    cmd.CommandText = sql.TrimEnd(',');
                    cmd.ExecuteNonQuery();
                    conn.Close();
                }
            }
        }

        public PagedList<CodeManage_CodeRelate> GetCodeRelatePageList(ref PagerModel pager, LogOnUserModel user, int action)
        {
            pager.FldName = @" * ";
            pager.TblName = @"(select a.*,b.BatchName,c.ProductName from codemanage_coderelate as a LEFT JOIN product_batch as b on a.ProductBatchId=b.ID LEFT JOIN product_product as c on a.ProductId=c.ID) z";
            pager.StrCondition = " EnterpriseId =" + user.EnterpriseId + " and Action=" + action;

            pager.FldSort = " ChangeDate desc ";
            MySqlParameter[] parameters = _baseRepository.SqParameterRefactor(ref pager);
            IEnumerable<CodeManage_CodeRelate> resultList = _baseRepository.ExecuteStoredProcedureCollection<CodeManage_CodeRelate>("Common_PageList", parameters);
            if (action == 1)
            {
                UpdateCodeRelationDisable(resultList, user);
            }
            return new PagedList<CodeManage_CodeRelate>(resultList, pager.Page, pager.PageSize, 1, Convert.ToInt32(parameters[6].Value));
        }

        /// <summary>
        /// 更新码禁用状态
        /// </summary>
        public void UpdateCodeRelationDisable(IEnumerable<CodeManage_CodeRelate> Model, LogOnUserModel UserModel)
        {
            var _tableName = string.Empty;
            if (Model.Count() > 0)
            {
                string _connectionString = ConfigurationManager.ConnectionStrings["mgj_codesql"].ConnectionString;
                if (string.IsNullOrWhiteSpace(_connectionString)) { return; }
                using (MySqlConnection conn = new MySqlConnection(_connectionString))
                {
                    using (MySqlCommand cmd = conn.CreateCommand())
                    {
                        conn.Open();
                        cmd.CommandType = CommandType.Text;
                        var batch = string.Empty;

                        foreach (CodeManage_CodeRelate item in Model)
                        {
                            if (item.CodeBatch == null)
                            {
                                var subcode = item.WL_CodeDisable.Substring(0, AppConst.SubStrLen);
                                _tableName = string.Format("antifake_digitcode{0}", subcode);
                            }
                            else
                            {
                                _tableName = string.Format("antifake_digitcode{0}{1}", UserModel.ECode, item.CodeBatch);
                            }
                            cmd.CommandText = string.Format("SHOW TABLES LIKE '{0}'", _tableName);
                            Object _Result = cmd.ExecuteScalar();
                            if (_Result == null) { continue; }
                            string _Where = string.Empty;
                            if (item.StartCode != 0 && item.EndCode != 0)
                            {
                                _Where = string.Format("  WHERE   ProductId = {0} AND ProductBatchId = {1} AND ID >={2} AND ID<={3}  ", item.ProductId, item.ProductBatchId, item.StartCode, item.EndCode);
                            }
                            if (item.WL_CodeDisable != null)
                            {
                                _Where = string.Format("  WHERE	Wl_Code IN ({0})", item.WL_CodeDisable);
                            }
                            cmd.CommandText = string.Format("SELECT Stats FROM  {0}   {1} LIMIT 1", _tableName, _Where);
                            _Result = cmd.ExecuteScalar();
                            if (_Result == null) { continue; }
                            int _Status = 0;
                            int.TryParse(Convert.ToString(_Result), out _Status);
                            item.Status = _Status;
                        }
                    }
                }
            }
        }

        public CodeManage_GenCode DistributorList(string str, CodeManage_CodeRelate model)
        {
            string _sql = string.Format("select Gen_Code_Nums ,EnterpriseId,'{1}' as BatchName  from CodeManage_GenCode where EnterpriseId={0} " +
      " and PatchId= (select ID from CodeManage_GenCodeBatch where  EnterpriseId={0} and Batch={1})", model.EnterpriseId, model.CodeBatch);
            var _model = _baseRepository.ExecuteText<CodeManage_GenCode>(_sql);
            string sql = "";
            if (model.RelateType == 1)//整批
            {
                sql = string.Format("select DISTINCT DistributorID as Id,FormCode,COUNT(wlcode) as WlCodeNum,a.`Name`,b.`name` as SaleArea from ( " +
   " select * from (SELECT * from antifleeing_outbounddetails where wlcode like'{0}%'  ORDER BY ID DESC ) W  WHERE wlcode like'{0}%' GROUP BY wlcode " +
   " ) m LEFT JOIN antifleeing_distributor a on a.id=m.DistributorID LEFT JOIN antifleeing_salesarea b on a.salesareaId=b.id ", str);
            }
            else
            {
                sql = string.Format("select DISTINCT DistributorID as Id,FormCode,COUNT(wlcode) as WlCodeNum,a.`Name`,b.`name` as SalesArea from ( " +
      " select * from (SELECT * from antifleeing_outbounddetails where wlcode in({0})  ORDER BY ID DESC ) W  WHERE wlcode in({0}) GROUP BY wlcode " +
      " ) m LEFT JOIN antifleeing_distributor a on a.id=m.DistributorID LEFT JOIN antifleeing_salesarea b on a.salesareaId=b.id ", model.WL_CodeDisable.Replace('，', ','));
            }

            var DistributorList = _baseRepository.ExecuteSqlCollection<DistributorSalesOutbounddetailsViewModel>(sql).ToList();
            if (DistributorList != null)
            {
                _model.DistributorList = DistributorList;
            }

            return _model;
        }

        public List<AntiFleeing_Detail> LookDetail(string formCode, string tableName)
        {
            string sql = string.Format("SELECT a.*,b.ProductId,c.ProductName from (select * from antifleeing_outbounddetails where FormCode='{0}') a " +
    " LEFT JOIN {1} b on a.wlcode=b.Wl_Code LEFT JOIN product_product c on b.ProductId=c.ID", formCode, tableName);

            return _baseRepository.ExecuteSqlCollection<AntiFleeing_Detail>(sql).ToList();
        }

        public PagedList<Antifleeing_DistributorRecallLog> GetCodeRecallDetailPageList(ref PagerModel pager, LogOnUserModel user)
        {
            pager.FldName = @" * ";
            pager.TblName = @"(select  a.*,b.CodeBatch,b.ChangeDate,b.RelateType,b.Remark,b.WL_CodeDisable from antifleeing_distributorrecalllog a left join codemanage_coderelate b on a.LogId=b.ID  where   a.DistributorId=" + user.Distributor + ") z";
            pager.StrCondition = " 1=1";

            pager.FldSort = " Id desc ";
            MySqlParameter[] parameters = _baseRepository.SqParameterRefactor(ref pager);
            IEnumerable<Antifleeing_DistributorRecallLog> resultList = _baseRepository.ExecuteStoredProcedureCollection<Antifleeing_DistributorRecallLog>("Common_PageList", parameters);
            return new PagedList<Antifleeing_DistributorRecallLog>(resultList, pager.Page, pager.PageSize, 1, Convert.ToInt32(parameters[6].Value));
        }

        public void GetbatchCodeFirstOrDefault(int enterpriseId, string codeBatch)
        {
        }
    }
}